package org.book.util;

import java.lang.reflect.Field;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import org.book.dao.BorrowBean;

public class JDBC_Util {
	public static void main(String[] args) {
		SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		//获取当前时间
		String DateStr1 = "2011-10-1 10:20:16";
		Date myDate1 = null, myDate2 = new Date();
		try {
			myDate1 = formatter.parse(DateStr1);
		} catch (ParseException e) {
			e.printStackTrace();
		}
		int i = myDate1.compareTo(myDate2);
		System.out.println(i);

				/*
				Date date = new Date();
				String time = formatter.format(date);
				date.setDate(date.getDate()+3);
				String time2 = formatter.format(date);
				System.out.println(time);
				System.out.println(time2);*/
		// 通过applyPattern来设定格式
		        /*format.applyPattern("yyyy-MM-dd HH:mm:ss");

		        //字符串转时间
		        String myTimeStr  = "2019-11-01";
		        try {
		            Date myDate = format.parse(myTimeStr);
		            System.out.println(myDate);
		        } catch (ParseException e) {
		            e.printStackTrace();
		        }

		        //时间转字符串
		        Date currentDate = new Date();
		        String currentDateStr = format.format(currentDate);
		        System.out.println(currentDateStr);*/
		/*String sql = "insert into rent_record values(?,?,?,?,?)";
		for (int i = 0; i < 99; i++) {
			superUpdate(sql, 1 + i, new java.sql.Date(new java.util.Date().getTime()),
					new java.sql.Date(1844651541651L), i + 1, i + 1);
		}*/

	}

	public static Connection getConnection() {
		Connection conn = null;
		try {
			// 自动注册加载
			Class.forName("com.mysql.jdbc.Driver");

			String url = "jdbc:mysql://localhost:3306/superb";
			String user = "root";
			String password = "123456.a";

			conn = DriverManager.getConnection(url, user, password);
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}

		return conn;
	}

	// 通用查询结果集个数(未考虑事务)
	public static int superSelect(String sql, Object... args) {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			conn = getConnection();
			ps = conn.prepareStatement(sql);
			for (int i = 0; i < args.length; i++) {
				ps.setObject(i + 1, args[i]);
			}
			// 获取结果集
			rs = ps.executeQuery();
			if (rs.next()) {
				return rs.getInt(1);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			JDBC_Util.closeResource(conn, ps, rs);
		}
		return 0;
	}

	public static List<BorrowBean> getList(String sql, Object... args){
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		List<BorrowBean> list = new ArrayList<>();
		try {
			conn = getConnection();
			ps = conn.prepareStatement(sql);
			for (int i = 0; i < args.length; i++) {
				ps.setObject(i + 1, args[i]);
			}
			// 获取结果集
			rs = ps.executeQuery();
			while (rs.next()) {
				BorrowBean bean = null;

				if(args.length==2||args.length==1||args.length==7) {
					//借阅详情
					bean = new BorrowBean(rs.getInt(1),rs.getObject(2).toString(),rs.getObject(3).toString(),rs.getString(4),rs.getString(5),rs.getString(6),rs.getString(7));
				}
				if(args.length==3||args.length==8){
					//用户详情
					bean = new BorrowBean(rs.getInt(1),rs.getString(2),rs.getString(3),rs.getDouble(4),rs.getString(5),rs.getString(6),rs.getObject(7).toString(),rs.getObject(8).toString());
				}
				list.add(bean);
			}

		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			JDBC_Util.closeResource(conn, ps, rs);
		}
		return list;
	}

	// 通用查询(未考虑事务)
	public static <T> List<T> superSelect(Class<T> c, String sql, Object... args) {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		List<T> list = new ArrayList<T>();
		try {
			conn = JDBC_Util.getConnection();
			ps = conn.prepareStatement(sql);
			for (int i = 0; i < args.length; i++) {
				ps.setObject(i + 1, args[i]);
			}
			// 获取结果集
			rs = ps.executeQuery();
			// 获取结果集元数据
			ResultSetMetaData rsmd = rs.getMetaData();
			// 获取元数据列数
			int count = rsmd.getColumnCount();
			while (rs.next()) {
				T t = c.newInstance();
				// 处理每行数据
				for (int i = 0; i < count; i++) {
					// 获取属性值
					Object objValue = rs.getObject(i + 1);
					// 获取属性名
					String columnName = rsmd.getColumnLabel(i + 1);

					// getColumnName() 获取列名(不推荐)
					// getColumnLabel() 获取别名 无别名默认列名
					// 通过反射找到私有属性
					Field field = c.getDeclaredField(columnName);
					// 设置访问私有属性
					field.setAccessible(true);
					// 进行赋值
					field.set(t, objValue);

				}
				list.add(t);
			}

		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			JDBC_Util.closeResource(conn, ps, rs);
		}
		return list;
	}

	// 通用增删改(未考虑事务)
	public static int superUpdate(String sql, Object... args) {
		Connection conn = null;
		PreparedStatement ps = null;
		try {
			// 获取数据库连接
			conn = JDBC_Util.getConnection();
			// sql预编译
			ps = conn.prepareStatement(sql);
			for (int i = 0; i < args.length; i++) {
				ps.setObject(i + 1, args[i]);
			}
			// 执行
			// ps.execute();
			// 执行并返回影响行数
			return ps.executeUpdate();
		} catch (Exception e) {
			e.printStackTrace();
			// 资源关闭
		} finally {
			JDBC_Util.closeResource(conn, ps);
		}
		return 0;
	}

	// 关闭资源
	public static void closeResource(Connection conn, Statement ps) {

		try {
			if (conn != null) {
				conn.close();
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		try {
			if (ps != null) {
				ps.close();
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}

	}

	// 关闭资源
	public static void closeResource(Connection conn, Statement ps, ResultSet rs) {

		try {
			if (conn != null) {
				conn.close();
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		try {
			if (ps != null) {
				ps.close();
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		try {
			if (rs != null) {
				rs.close();
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}

	}
}
